import 'package:flutter_shopcar_test/data/test_data.dart';
import 'package:flutter_shopcar_test/database/method_dao.dart';
import 'package:flutter_shopcar_test/model/goods_list_model.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class SqliteDataManger {
  final int version = 1;
  final String shopDatabase = 'shopDatabase.db';

  /// 商品
  static const String goodsTableName = 'GoodsTable';
  static const String id = 'id';
  static const String goodsName = 'goodsName';
  static const String goodsCode = 'goodsCode';
  static const String goodsPrice = 'goodsPrice';
  static const String goodsQty = 'goodsQty';

  /// 购物车
  static const String shopCartTable = 'ShopCartTable';
  static const String shopCartName = 'shopCartName';
  static const String shopCartCode = 'shopCartCode';
  static const String shopCartPrice = 'shopCartPrice';
  static const String shopCartQty = 'shopCartQty';

  /// 订单
  static const String orderTable = 'OrderTable';
  static const String orderDoc = 'orderDoc';
  static const String orderTime = 'orderTime';
  static const String totalPrice = 'totalPrice';
  static const String payWay = 'payWay';

  /// 子订单商品
  static const String orderItemTable = 'OrderItemTable';
  static const String orderGoodsName = 'orderGoodsName';
  static const String orderGoodsCode = 'orderGoodsCode';
  static const String orderGoodsPrice = 'orderGoodsPrice';
  static const String orderGoodsQty = 'orderGoodsQty';
  static const String orderId = 'orderId';

  static SqliteDataManger? _instance;
  static SqliteDataManger getInstance() => _instance ??= SqliteDataManger();

  static Database? _database;
  Future<Database> get getDatabase async => _database ??= await _initSQl();

  ///初始化数据库
  Future<Database> _initSQl() async{
    var dbPath = await getDatabasesPath();
    var path = join(dbPath, shopDatabase);
    return await openDatabase(
      path,
      version: version,
      onCreate: _onCreate,
      onConfigure: (Database db) async {
        await db.execute('PRAGMA foreign_keys = ON');
      },
    );
  }

  static String sqlOrder = 'select * from $orderTable join $orderItemTable on OrderTable.orderId = OrderItemTable.orderId';

  Future _onCreate(Database db,int version) async {
    String goodsTableSql = '''
     CREATE TABLE $goodsTableName(
      $id INTEGER PRIMARY KEY autoincrement,
      $goodsName TEXT,
      $goodsCode TEXT,
      $goodsPrice INTEGER,
      $goodsQty INTEGER
      )
    ''';

    String shopCartTableSql = '''
     CREATE TABLE $shopCartTable(
      $id INTEGER PRIMARY KEY autoincrement,
      $shopCartName TEXT,
      $shopCartCode TEXT,
      $shopCartPrice INTEGER,
      $shopCartQty INTEGER
      )
    ''';

    String orderTableSql = ''' 
     CREATE TABLE $orderTable(
      $orderId INTEGER PRIMARY KEY autoincrement,
      $orderDoc TEXT,
      $orderTime TEXT,
      $totalPrice INTEGER,
      $payWay TEXT
     )
    ''';

    String orderItemTableSql = ''' 
     CREATE TABLE $orderItemTable(
      $id INTEGER PRIMARY KEY autoincrement,
      $orderGoodsName TEXT,
      $orderGoodsCode TEXT,
      $orderGoodsPrice INTEGER,
      $orderGoodsQty INTEGER,
      $orderId INTEGER,
      FOREIGN KEY($orderId) REFERENCES $orderTable($orderId)
     )
    ''';

    await db.transaction((Transaction txn) async {
      await txn.execute(goodsTableSql);
      await txn.execute(shopCartTableSql);
      await txn.execute(orderTableSql);
      await txn.execute(orderItemTableSql);
    });
  }

  Future<void> initData() async {
    List<Map<String, dynamic>> list = InitData.goodsList;
    if(list.isNotEmpty) {
      for (var e in list) {
        GoodsListModel model = GoodsListModel.fromJson(e);
        await MethodDao.insertGoodsList(model);
      }
    }
  }

  Future<void> closeDatabase() async {
    if (_database != null) {
      await _database!.close();
    }
  }


}